﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;

namespace CVSA.Suporte.Sinacor.BMFBovespa.ConsultaCorretoras
{
    public class DataLayer 
    {
        private OracleConnection conn  = new OracleConnection(ConfigurationManager.ConnectionStrings["connString"].ToString());

        public DataTable Tb_Corretoras()
        {
            DataTable odt = new DataTable();

            if (System.Web.HttpContext.Current.Cache["cacheCorretoras"] != null)
            {
                odt = (DataTable)System.Web.HttpContext.Current.Cache["cacheCorretoras"];
            }
            else
            {
                string strSql = @"SELECT     CD_CORRET, NM_CORRET || ' - ' || CD_NUMMEM NM_CORRET
                                  FROM         CACOUSU
                                  ORDER BY NM_CORRET";
                OracleDataAdapter da = new OracleDataAdapter(strSql, conn);

                try
                {
                    da.Fill(odt);
                    System.Web.HttpContext.Current.Cache["cacheCorretoras"] = odt;
                }
                finally
                {
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return odt;
        }

        public DataTable Tb_UsuariosCorretora()
        {
            DataTable odt = new DataTable();

            if (System.Web.HttpContext.Current.Cache["cacheUsuariosCorretora"] != null)
            {
                odt = (DataTable)System.Web.HttpContext.Current.Cache["cacheUsuariosCorretora"];
            }
            else
            {
                string strSql = @"SELECT     C.CD_CORRET, U.NM_USUARIO AS NOME, U.NM_TELEFONE AS TELEFONE, U.DS_MAIL AS ""E-MAIL"" , D.DS_DEPCOR AS ""DEPARTAMENTO""
                                FROM         CACOUSU C, CACOUSUCOR U
                                LEFT JOIN CACODEPCOR D ON D.CD_DEPCOR = U.CD_DEPCOR
                                WHERE       C.CD_CORRET = U.CD_CORRET AND ((U.NM_TELEFONE IS NOT NULL) AND (U.NM_USUARIO NOT LIKE '*%') OR
                                (U.NM_USUARIO NOT LIKE '*%') AND (U.DS_MAIL IS NOT NULL))
                                ORDER BY NOME";
                OracleDataAdapter da = new OracleDataAdapter(strSql, conn);

                try
                {
                    da.Fill(odt);
                    System.Web.HttpContext.Current.Cache["cacheUsuariosCorretora"] = odt;
                }
                finally
                {
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return odt;
        }

        public DataTable Tb_SistemasCorretora()
        {
            DataTable odt = new DataTable();

            if (System.Web.HttpContext.Current.Cache["cacheSistemasCorretora"] != null)
            {
                odt = (DataTable)System.Web.HttpContext.Current.Cache["cacheSistemasCorretora"];
            }
            else
            {
                string strSql = @"  SELECT     CACOSIST.NM_SISTEMA, CACOUSU.CD_CORRET
                                    FROM         CACOUSU, CACOSISTCORRET, CACOSIST
                                    WHERE     CACOUSU.CD_CORRET = CACOSISTCORRET.CD_CORRET AND CACOSISTCORRET.CD_SISTEMA = CACOSIST.CD_SISTEMA
                                    ORDER BY CACOSIST.NM_SISTEMA";
                OracleDataAdapter da = new OracleDataAdapter(strSql, conn);

                try
                {
                    da.Fill(odt);
                    System.Web.HttpContext.Current.Cache["cacheSistemasCorretora"] = odt;
                }
                finally
                {
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return odt;
        }

        public DataTable Tb_Sistemas()
        {
            DataTable odt = new DataTable();

            if (System.Web.HttpContext.Current.Cache["cacheSistemas"] != null)
            {
                odt = (DataTable)System.Web.HttpContext.Current.Cache["cacheSistemas"];
            }
            else
            {
                string strSql = @"  SELECT     CACOSIST.NM_SISTEMA, CACOSIST.CD_SISTEMA
                                    FROM       CACOSIST
                                    ORDER BY   CACOSIST.NM_SISTEMA";

                OracleDataAdapter da = new OracleDataAdapter(strSql, conn);

                try
                {
                    da.Fill(odt);
                    System.Web.HttpContext.Current.Cache["cacheSistemas"] = odt;
                }
                finally
                {
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return odt;
        }

        public DataTable Tb_Corretoras(string Sistemas)
        {
            DataTable odt = new DataTable();


            string strSql = @"  SELECT DISTINCT CACOSISTCORRET.CD_CORRET, CACOUSU.NM_CORRET
                            FROM         CACOSIST, CACOSISTCORRET, CACOUSU
                            WHERE     CACOSIST.CD_SISTEMA = CACOSISTCORRET.CD_SISTEMA AND CACOSISTCORRET.CD_CORRET = CACOUSU.CD_CORRET ";

            if (Sistemas != string.Empty)
            {
                strSql += " AND (CACOSIST.CD_SISTEMA IN (" + Sistemas + "))";
            }
            
            strSql += " ORDER BY CACOUSU.NM_CORRET";

            OracleDataAdapter da = new OracleDataAdapter(strSql, conn);

            try
            {
                da.Fill(odt);
            }
            finally
            {
                da.Dispose();
                conn.Dispose();
            }

            return odt;
        }
    }
}
